import requests
import re
import pymysql
import time

t1 = time.times()
# 打开数据库连接
db = pymysql.connect("localhost", "root", "123456", "test")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()


# 使用预处理语句创建表
ti = []
secdata  = open('C:/Users/yehuo/PycharmProjects/financial/sec.txt','r',encoding='utf-8')
for h in secdata.readlines():
    h.encode('utf-8').decode('utf-8-sig')
    h=h.replace('.XSHG','')
    h=h.replace('.XSHE','')
    h=h.replace('\n','')
    ti.append(h)
if ti[-1]=="":
    ti.pop(-1)
print (ti)
for k in ti:
    ticker = k
    # 使用 execute() 方法执行 SQL，如果表存在则删除
    cursor.execute("DROP TABLE IF EXISTS `%s`"%(ticker))
    sql = """CREATE TABLE `%s` (DATETIME  CHAR(20) ,KFINCOME CHAR(20) )"""%(ticker)
    cursor.execute(sql)

    url = 'http://quotes.money.163.com/f10/zycwzb_%s.html#01c01'%(ticker)
    print (url)
    response = requests.get(url)
    fhandle = open('C:/Users/yehuo/PycharmProjects/financial/a.html','w',encoding='utf-8')
    data = response.text
    data = str.encode(data)
    data = str(data,encoding='utf-8')
    fhandle.write(data)
    fhandle.close()

    #获取数据
    fhandle = open('C:/Users/yehuo/PycharmProjects/financial/a.html','r',encoding='utf-8')
    data = fhandle.read()
    pattern = re.compile('<table class="table_bg001 border_box limit_sale scr_table" >.*</table>',re.S)
    result = pattern.findall(data)
    fh2 = open('C:/Users/yehuo/PycharmProjects/financial/b.txt','w',encoding='utf-8')
    fh2.write(result[0])
    fh2.close()

    #筛选时间 插入数据
    fh2=open('C:/Users/yehuo/PycharmProjects/financial/b.txt','r',encoding='utf-8')
    line = fh2.read().splitlines()
    pattern = re.compile('<tr class="dbrow"> <th >(.*)</th> </tr>')
    res = pattern.findall(line[2])
    res = res[0]
    res = res.split('</th><th >')
    print(len(res),res[0],res[1],res[-1],res[-2])
    pattern = re.compile('<tr>(.*)</td></tr>')
    res2 = pattern.findall(line[24])
    res2 = res2[0]
    res2 = res2.replace("</td><td class='cRed'>","</td><td>")
    res2 = res2.replace("<td class='cRed'>","")
    res2 = res2.split('</td><td>')
    res2[0]=res2[0].replace('<td>','')
    print(len(res2),res2[0],res2[1],res2[-1],res2[-2])
    final = zip(res,res2)
    for i in final:
        # SQL 插入语句
        sql = """INSERT INTO `%s`(DATETIME,KFINCOME)VALUES ('%s','%s')"""%(ticker,i[0],i[1])
        cursor.execute(sql)
            # 提交到数据库执行
        db.commit()


t2 = time.time()
print (t2-t1